﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;


/// <summary>
/// Summary description for Connectdbnour
/// </summary>
public class Connectdbnour
{
    SqlConnection con;
    public Connectdbnour()
    {
        con = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=nourcenter");
    }
    public SqlConnection connect()
    {
        return con;
    }
    /*public int Countcomp()
    {
        int x = 0;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from complaint", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            x = (int)dv2[0][0];
        }
        con.Close();
        return x;
    }
    public String[] selectcomp()
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select * from complaint", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from complaint", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            int x = (int)dv2[0][0];
            array = new String[x];
            for (int i = 0; i < x; i++)
            {
                array[i] = dv1[i][0].ToString();
            }
        }
        con.Close();
        return array;
    }*/
    public void insertcomp(String comp)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into complaint values (@comp)";
            sqlcom.Parameters.Add("@comp", comp);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Removecompdata(String comp)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "delete from complaint where comp = @comp";
            sqlcom.Parameters.Add("@comp", comp);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    /*public int Countfind()
    {
        int x = 0;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from finding", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            x = (int)dv2[0][0];
        }
        con.Close();
        return x;
    }
    public String[] selectfind()
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select * from finding", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from finding", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            int x = (int)dv2[0][0];
            array = new String[x];
            for (int i = 0; i < x; i++)
            {
                array[i] = dv1[i][0].ToString();
            }
        }
        con.Close();
        return array;
    }*/
    public void insertfind(String find)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into finding values (@find)";
            sqlcom.Parameters.Add("@find", find);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Removefinddata(String find)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "delete from finding where finddata = @find";
            sqlcom.Parameters.Add("@find", find);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    /*public int Countdiag()
    {
        int x = 0;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from Diagnosis", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            x = (int)dv2[0][0];
        }
        con.Close();
        return x;
    }
    public String[] selectdiag()
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select * from Diagnosis", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from Diagnosis", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            int x = (int)dv2[0][0];
            array = new String[x];
            for (int i = 0; i < x; i++)
            {
                array[i] = dv1[i][0].ToString();
            }
        }
        con.Close();
        return array;
    }*/
    public void insertdiag(String diag)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into Diagnosis values (@diag)";
            sqlcom.Parameters.Add("@diag", diag);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Removediagdata(String diag)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "delete from Diagnosis where diag = @diag";
            sqlcom.Parameters.Add("@diag", diag);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public int Countbrand()
    {
        int x = 0;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from brandname", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            x = (int)dv2[0][0];
        }
        con.Close();
        return x;
    }
    /*public String[] selectbrand()
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select * from brandname", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from brandname", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            int x = (int)dv2[0][0];
            array = new String[x];
            for (int i = 0; i < x; i++)
            {
                array[i] = dv1[i][0].ToString();
            }
        }
        con.Close();
        return array;
    }*/
    public void insertbrand(String brand)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into brandname values (@brand)";
            sqlcom.Parameters.Add("@brand", brand);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Removebranddata(String brand)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "delete from brandname where name = @brand";
            sqlcom.Parameters.Add("@brand", brand);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    /*public int Countcolor()
    {
        int x = 0;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from color", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            x = (int)dv2[0][0];
        }
        con.Close();
        return x;
    }
    public String[] selectcolor()
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select * from color", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from color", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            int x = (int)dv2[0][0];
            array = new String[x];
            for (int i = 0; i < x; i++)
            {
                array[i] = dv1[i][0].ToString();
            }
        }
        con.Close();
        return array;
    }*/
    public void insertcolor(String color)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into color values (@color)";
            sqlcom.Parameters.Add("@color", color);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Removecolordata(String color)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "delete from color where color = @color";
            sqlcom.Parameters.Add("@color", color);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void insertsup(String name ,String address ,Int32 mobile ,Int32 tel ,Int32 fax ,String account ,String notes)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into supplier values (@name,@address,@mobile,@tel,@fax,@account,@notes)";
            sqlcom.Parameters.Add("@name", name);
            sqlcom.Parameters.Add("@address", address);
            sqlcom.Parameters.Add("@mobile", mobile);
            sqlcom.Parameters.Add("@tel", tel);
            sqlcom.Parameters.Add("@fax", fax);
            sqlcom.Parameters.Add("@account", account);
            sqlcom.Parameters.Add("@notes", notes);
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }

    public int Countsup()
    {
        int x = 0;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from supplier", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            x = (int)dv2[0][0];
        }
        con.Close();
        return x;
    }
    public String[] selectsup()
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select * from supplier", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            SqlDataAdapter da2 = new SqlDataAdapter("select COUNT(*) from supplier", con);
            DataTable dt2 = new DataTable();
            DataView dv2;
            da2.Fill(dt2);
            dv2 = dt2.DefaultView;
            int x = (int)dv2[0][0];
            array = new String[x];
            for (int i = 0; i < x; i++)
            {
                array[i] = dv1[i][1].ToString();
            }
        }
        con.Close();
        return array;
    }
    public String[] showsup(String name)
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select * from supplier where supplyername = '" + name + "'", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            array = new String[8];
            for (int i = 0; i < 8; i++)
            {
                array[i] = dv1[0][i].ToString();
            }
        }
        con.Close();
        return array;
    }
    public void Addflens(String brand, String vd, String color, Int16 pck, Double sph, Double cyl, Double axis, Double bc, Double diam, String lot, String expire, Int16 store1)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "Insert into flensstored values('" + brand + "','" + vd + "','" + color + "'," + pck + "," + sph + "," + cyl + "," + axis + "," + bc + "," + diam + ",'" + lot + "','" + expire + "'," + store1 + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfsoln(String brand, Int16 bottlesize, Int16 store1)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "Insert into fsolnstored values('" + brand + "'," + bottlesize + "," + store1 + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfaccs(String name, Int16 store1)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "Insert into faccstored values('" + name + "'," + store1 + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfklens(String brand, String vd, String color, Int16 pck, Double sph, Double cyl, Double axis, Double bc, Double diam, String lot, String expire, Int16 store1)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into fklensstored values('" + brand + "','" + vd + "','" + color + "'," + pck + "," + sph + "," + cyl + "," + axis + "," + bc + "," + diam + ",'" + lot + "','" + expire + "'," + store1 + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfksoln(String brand, Int16 bottlesize, Int16 store1)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "Insert into fksolnstored values('" + brand + "'," + bottlesize + "," + store1 + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfkaccs(String name, Int16 store1)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "Insert into fkaccstored values('" + name + "'," + store1 + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public Int32 newclient(String firstname, String middlename, String lastname, String db, String job, String addres, Int32 mobile, Int32 home, String how, String did, String kindoflens)
    {
        int code = 0;
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_client";
            sqlcom.Parameters.Add(new SqlParameter("@firstname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@middlename", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@lastname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@db", SqlDbType.Date, 0));
            sqlcom.Parameters.Add(new SqlParameter("@job", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@addres", SqlDbType.NVarChar, 1000));
            sqlcom.Parameters.Add(new SqlParameter("@mobile", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@home", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@how", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@did", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@kind", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@code", SqlDbType.Int, 0));
            sqlcom.Parameters["@firstname"].Value = firstname;
            sqlcom.Parameters["@middlename"].Value = middlename;
            sqlcom.Parameters["@lastname"].Value = lastname;
            sqlcom.Parameters["@db"].Value = db;
            sqlcom.Parameters["@job"].Value = job;
            sqlcom.Parameters["@addres"].Value = addres;
            sqlcom.Parameters["@mobile"].Value = mobile;
            sqlcom.Parameters["@home"].Value = home;
            sqlcom.Parameters["@how"].Value = how;
            sqlcom.Parameters["@did"].Value = did;
            sqlcom.Parameters["@kind"].Value = kindoflens;
            sqlcom.Parameters["@code"].Direction = ParameterDirection.Output;
            sqlcom.ExecuteNonQuery();
            code = Int32.Parse(sqlcom.Parameters["@code"].Value.ToString());
        }
        con.Close();
        return code;
    }
    public String[] ftos(String bill, String date, String Supplyername, Double unitprice, Double unitone, Double unittwo, Double unitthree, Double discount)
    {
        String[] array = null;
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_ftos";
            sqlcom.Parameters.Add(new SqlParameter("@bill", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@datee", SqlDbType.Date, 0));
            sqlcom.Parameters.Add(new SqlParameter("@supplyername", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@unitprice", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@unitone", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@unittwo", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@unitthree", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@discount", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@sum", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@total", SqlDbType.Float, 0));
            sqlcom.Parameters["@bill"].Value = bill;
            sqlcom.Parameters["@datee"].Value = date;
            sqlcom.Parameters["@supplyername"].Value = Supplyername;
            sqlcom.Parameters["@unitprice"].Value = unitprice;
            sqlcom.Parameters["@unitone"].Value = unitone;
            sqlcom.Parameters["@unittwo"].Value = unittwo;
            sqlcom.Parameters["@unitthree"].Value = unitthree;
            sqlcom.Parameters["@discount"].Value = discount;
            sqlcom.Parameters["@sum"].Direction = ParameterDirection.Output;
            sqlcom.Parameters["@total"].Direction = ParameterDirection.Output;
            sqlcom.ExecuteNonQuery();
            array = new String[2];
            array[0] = sqlcom.Parameters["@sum"].Value.ToString();
            array[1] = sqlcom.Parameters["@total"].Value.ToString();
        }
        con.Close();
            return array;
    }
    public String[] codetoname(Int32 code)
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select * from client where code = " + code, con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            array = new String[12];
            for (int i = 0; i < 12; i++)
            {
                array[i] = dv1[0][i].ToString();
            }
        }
        con.Close();
        return array;
    }
    public String[] ShowBill(String Bill)
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select * from tasdeed where bill='" + Bill + "'", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            array = new String[8];
            for (int i = 0; i < 8; i++)
            {
                array[i] = dv1[0][i].ToString();
            }
        }
        con.Close();
        return array;
    }
    public void insertplan(String Bill, String payment, String date, String number, Double cost, String bank, Int16 premium)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "Insert into payment values('" + Bill + "','" + payment + "','" + date + "','" + number + "'," + cost + ",'" + bank + "'," + premium + ",'No')";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public Double countsupindebt(String supplier)
    {
        Double x = 0;
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select sum(Indebtedness) from tasdeed where supplyername ='" + supplier + "'", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            x = Convert.ToDouble(dv1[0][0].ToString());
        }
        con.Close();
        return x;
    }
    public void tasdeedmnelplan(String bill, Int16 premium)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_tasdeed";
            sqlcom.Parameters.Add(new SqlParameter("@bill", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@premium", SqlDbType.Int, 0));
            sqlcom.Parameters["@bill"].Value = bill;
            sqlcom.Parameters["@premium"].Value = premium;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public String oldclientcode(String first, String middle, String last)
    {
        String code = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select code from client where firstname='" + first + "' and middlename='" + middle + "' and lastname='" + last + "'", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            code = dv1[0][0].ToString();
        }
        con.Close();
        return code;
    }
    public void insertrecep(String emp, String date, Int32 client, String kind, String doctor, Double cost, Int16 store)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "Insert into reception values('" + emp + "','" + date + "'," + client + ",'" + kind + "','" + doctor + "'," + cost + "," + store + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfclens(String emp, String date, Int32 client, String brand, String pow, String vd, String color, Int16 pck, Int16 ampck, Double sph, Double cyl, Double axis, Double bc, Double diam, Int16 amount, Double cost)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into fclens values ('" + emp + "','" + date + "'," + client + ",'" + brand + "','" + pow + "','" + vd + "','" + color + "'," + pck + "," + ampck + "," + sph + "," + cyl + "," + axis + "," + bc + "," + diam + "," + amount + "," + cost + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfclens2(String emp, String date, Int32 client, String brand, String pow, String vd, String color, Int16 pck, Int16 ampck, Double sph, Double cyl, Double axis, Double bc, Double diam, Int16 amount, Double cost)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into fclens2 values ('" + emp + "','" + date + "'," + client + ",'" + brand + "','" + pow + "','" + vd + "','" + color + "'," + pck + "," + ampck + "," + sph + "," + cyl + "," + axis + "," + bc + "," + diam + "," + amount + "," + cost + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void fctoclens()
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_fclientlens";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void fctoclens2()
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_fclientlens2";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void fctocsoln()
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_fclientsoln";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void fctocsoln2()
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_fclientsoln2";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfcsoln(String emp, String date, Int32 client, String brand, Int16 bottlesize, Double cost)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into fcsoln values ('" + emp + "','" + date + "'," + client + ",'" + brand + "'," + bottlesize + "," + cost + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfcsoln2(String emp, String date, Int32 client, String brand, Int16 bottlesize, Double cost)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into fcsoln2 values ('" + emp + "','" + date + "'," + client + ",'" + brand + "'," + bottlesize + "," + cost + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfcaccs(String emp, String date, Int32 client, String brand, Double cost)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into fcaccs values ('" + emp + "','" + date + "'," + client + ",'" + brand + "'," + cost + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addfcaccs2(String emp, String date, Int32 client, String brand, Double cost)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into fcaccs2 values ('" + emp + "','" + date + "'," + client + ",'" + brand + "'," + cost + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addrsvdr(String emp, String date, Int32 client, String kind, String doctor, String dateclinic, Int16 payed, Int16 rest)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into reservclinic values ('" + emp + "','" + date + "'," + client + ",'" + kind + "','" + doctor + "','" + dateclinic + "'," + payed + "," + rest + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addrsvdr2(String emp, String date, Int32 client, String kind, String doctor, String dateclinic, Int16 payed, Int16 rest)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into reservclinic2 values ('" + emp + "','" + date + "'," + client + ",'" + kind + "','" + doctor + "','" + dateclinic + "'," + payed + "," + rest + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addrsvlens(String emp, String date, Int32 client, String kind, String brand, String color, Double sph, Double cyl, Double axis, Double bc, Double diam, Int16 payed, Int16 rest)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into reservlens values ('" + emp + "','" + date + "'," + client + ",'" + kind + "','" + brand + "','" + color + "'," + sph + "," + cyl + "," + axis + "," + bc + "," + diam + "," + payed + "," + rest + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void Addrsvlens2(String emp, String date, Int32 client, String kind, String brand, String color, Double sph, Double cyl, Double axis, Double bc, Double diam, Int16 payed, Int16 rest)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into reservlens2 values ('" + emp + "','" + date + "'," + client + ",'" + kind + "','" + brand + "','" + color + "'," + sph + "," + cyl + "," + axis + "," + bc + "," + diam + "," + payed + "," + rest + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void insertemployee(String name, String job, String Address, Int32 mobile, Int32 tel)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = "insert into employee values ('" + name + "','" + job + "','" + Address + "'," + mobile + "," + tel + ")";
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void S1TOS2LENS(String brand, String vd, String color, Int16 pck, Double sph, Double cyl, Double axis, Double bc, Double diam, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_S1TOS2LENS";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@vd", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@color", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@pck", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@sph", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@cyl", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@axis", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@bc", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@diam", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@vd"].Value = vd;
            sqlcom.Parameters["@color"].Value = color;
            sqlcom.Parameters["@pck"].Value = pck;
            sqlcom.Parameters["@sph"].Value = sph;
            sqlcom.Parameters["@cyl"].Value = cyl;
            sqlcom.Parameters["@axis"].Value = axis;
            sqlcom.Parameters["@bc"].Value = bc;
            sqlcom.Parameters["@diam"].Value = diam;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void S2TOS1LENS(String brand, String vd, String color, Int16 pck, Double sph, Double cyl, Double axis, Double bc, Double diam, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_S2TOS1LENS";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@vd", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@color", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@pck", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@sph", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@cyl", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@axis", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@bc", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@diam", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@vd"].Value = vd;
            sqlcom.Parameters["@color"].Value = color;
            sqlcom.Parameters["@pck"].Value = pck;
            sqlcom.Parameters["@sph"].Value = sph;
            sqlcom.Parameters["@cyl"].Value = cyl;
            sqlcom.Parameters["@axis"].Value = axis;
            sqlcom.Parameters["@bc"].Value = bc;
            sqlcom.Parameters["@diam"].Value = diam;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void dels1lens(String brand, String vd, String color, Int16 pck, Double sph, Double cyl, Double axis, Double bc, Double diam, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_dels1lens";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@vd", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@color", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@pck", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@sph", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@cyl", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@axis", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@bc", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@diam", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@vd"].Value = vd;
            sqlcom.Parameters["@color"].Value = color;
            sqlcom.Parameters["@pck"].Value = pck;
            sqlcom.Parameters["@sph"].Value = sph;
            sqlcom.Parameters["@cyl"].Value = cyl;
            sqlcom.Parameters["@axis"].Value = axis;
            sqlcom.Parameters["@bc"].Value = bc;
            sqlcom.Parameters["@diam"].Value = diam;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void dels2lens(String brand, String vd, String color, Int16 pck, Double sph, Double cyl, Double axis, Double bc, Double diam, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_dels2lens";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@vd", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@color", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@pck", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@sph", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@cyl", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@axis", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@bc", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@diam", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@vd"].Value = vd;
            sqlcom.Parameters["@color"].Value = color;
            sqlcom.Parameters["@pck"].Value = pck;
            sqlcom.Parameters["@sph"].Value = sph;
            sqlcom.Parameters["@cyl"].Value = cyl;
            sqlcom.Parameters["@axis"].Value = axis;
            sqlcom.Parameters["@bc"].Value = bc;
            sqlcom.Parameters["@diam"].Value = diam;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public String insertlenss1(String brand, String vd, String color, Int16 pck, Double sph, Double cyl, Double axis, Double bc, Double diam, Int16 amount)
    {
        String s = null;
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_insertlenss1";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@vd", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@color", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@pck", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@sph", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@cyl", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@axis", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@bc", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@diam", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@return", SqlDbType.NVarChar, 100));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@vd"].Value = vd;
            sqlcom.Parameters["@color"].Value = color;
            sqlcom.Parameters["@pck"].Value = pck;
            sqlcom.Parameters["@sph"].Value = sph;
            sqlcom.Parameters["@cyl"].Value = cyl;
            sqlcom.Parameters["@axis"].Value = axis;
            sqlcom.Parameters["@bc"].Value = bc;
            sqlcom.Parameters["@diam"].Value = diam;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.Parameters["@return"].Direction = ParameterDirection.Output;
            sqlcom.ExecuteNonQuery();
            s = sqlcom.Parameters["@return"].Value.ToString();
        }
        con.Close();
        return s;
    }
    public String insertlenss2(String brand, String vd, String color, Int16 pck, Double sph, Double cyl, Double axis, Double bc, Double diam, Int16 amount)
    {
        String s = null;
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_insertlenss2";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@vd", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@color", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@pck", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@sph", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@cyl", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@axis", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@bc", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@diam", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@return", SqlDbType.NVarChar, 100));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@vd"].Value = vd;
            sqlcom.Parameters["@color"].Value = color;
            sqlcom.Parameters["@pck"].Value = pck;
            sqlcom.Parameters["@sph"].Value = sph;
            sqlcom.Parameters["@cyl"].Value = cyl;
            sqlcom.Parameters["@axis"].Value = axis;
            sqlcom.Parameters["@bc"].Value = bc;
            sqlcom.Parameters["@diam"].Value = diam;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.Parameters["@return"].Direction = ParameterDirection.Output;
            sqlcom.ExecuteNonQuery();
            s = sqlcom.Parameters["@return"].Value.ToString();
        }
        con.Close();
        return s;
    }
    public void S1TOS2soln(String brand, Int16 bottle, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_S1TOS2soln";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@bottlesize", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@bottlesize"].Value = bottle;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void S2TOS1soln(String brand, Int16 bottle, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_S2TOS1soln";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@bottlesize", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@bottlesize"].Value = bottle;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void dels1soln(String brand, Int16 bottle, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_dels1soln";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@bottlesize", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@bottlesize"].Value = bottle;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void dels2soln(String brand, Int16 bottle, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_dels2soln";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@bottlesize", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@bottlesize"].Value = bottle;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public String insertsolns1(String brand, Int16 bottle, Int16 amount)
    {
        String s = null;
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_insertsolns1";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@bottlesize", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@return", SqlDbType.NVarChar, 100));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@bottlesize"].Value = bottle;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.Parameters["@return"].Direction = ParameterDirection.Output;
            sqlcom.ExecuteNonQuery();
            s = sqlcom.Parameters["@return"].Value.ToString();
        }
        con.Close();
        return s;
    }
    public String insertsolns2(String brand, Int16 bottle, Int16 amount)
    {
        String s = null;
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_insertsolns2";
            sqlcom.Parameters.Add(new SqlParameter("@brandname", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@bottlesize", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@return", SqlDbType.NVarChar, 100));
            sqlcom.Parameters["@brandname"].Value = brand;
            sqlcom.Parameters["@bottlesize"].Value = bottle;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.Parameters["@return"].Direction = ParameterDirection.Output;
            sqlcom.ExecuteNonQuery();
            s = sqlcom.Parameters["@return"].Value.ToString();
        }
        con.Close();
        return s;
    }
    public void S1TOS2accs(String name, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_S1TOS2accs";
            sqlcom.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@name"].Value = name;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void S2TOS1accs(String name, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_S2TOS1accs";
            sqlcom.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@name"].Value = name;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void dels1accs(String name, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_dels1accs";
            sqlcom.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@name"].Value = name;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void dels2accs(String name, Int16 amount)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_dels2accs";
            sqlcom.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters["@name"].Value = name;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public String insertaccss1(String name, Int16 amount)
    {
        String s = null;
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_insertaccss1";
            sqlcom.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@return", SqlDbType.NVarChar, 100));
            sqlcom.Parameters["@name"].Value = name;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.Parameters["@return"].Direction = ParameterDirection.Output;
            sqlcom.ExecuteNonQuery();
            s = sqlcom.Parameters["@return"].Value.ToString();
        }
        con.Close();
        return s;
    }
    public String insertaccss2(String name, Int16 amount)
    {
        String s = null;
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_insertaccss2";
            sqlcom.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@amount", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@return", SqlDbType.NVarChar, 100));
            sqlcom.Parameters["@name"].Value = name;
            sqlcom.Parameters["@amount"].Value = amount;
            sqlcom.Parameters["@return"].Direction = ParameterDirection.Output;
            sqlcom.ExecuteNonQuery();
            s = sqlcom.Parameters["@return"].Value.ToString();
        }
        con.Close();
        return s;
    }
    public String[] Showpricelenspowers(String brand, String color)
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select unitprice,clientprice,doctorprice from lens where brandname='" + brand + "' and color='" + color + "' and not(sph=0 and cyl=0 and axis=0)", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            array = new String[3];
            for (int i = 0; i < 3; i++)
            {
                array[i] = dv1[0][i].ToString();
            }
        }
        con.Close();
        return array;
    }
    public void Updatepricelenspowers(String brand, String color, Int16 client, Int16 doctor)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            SqlCommand sqlcom1 = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom1.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom1.CommandType = CommandType.Text;
            sqlcom.CommandText = "update lens set clientprice=" + client + " where brandname='" + brand + "' and color='" + color + "' and not(sph=0 and cyl=0 and axis=0)";
            sqlcom1.CommandText = "update lens set doctorprice=" + doctor + " where brandname='" + brand + "' and color='" + color + "' and not(sph=0 and cyl=0 and axis=0)";
            sqlcom.ExecuteNonQuery();
            sqlcom1.ExecuteNonQuery();
        }
        con.Close();
    }
    public String[] Showpricelensplano(String brand, String color)
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select unitprice,clientprice,doctorprice from lens where brandname='" + brand + "' and color='" + color + "' and sph=0 and cyl=0 and axis=0", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            array = new String[3];
            for (int i = 0; i < 3; i++)
            {
                array[i] = dv1[0][i].ToString();
            }
        }
        con.Close();
        return array;
    }
    public void Updatepricelensplano(String brand, String color, Int16 client, Int16 doctor)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            SqlCommand sqlcom1 = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom1.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom1.CommandType = CommandType.Text;
            sqlcom.CommandText = "update lens set clientprice=" + client + " where brandname='" + brand + "' and color='" + color + "' and sph=0 and cyl=0 and axis=0";
            sqlcom1.CommandText = "update lens set doctorprice=" + doctor + " where brandname='" + brand + "' and color='" + color + "' and sph=0 and cyl=0 and axis=0";
            sqlcom.ExecuteNonQuery();
            sqlcom1.ExecuteNonQuery();
        }
        con.Close();
    }
    public String[] Showpriceboxpowers(String brand, Int16 pck, String color)
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select unitprice,clientprice,doctorprice from box where brandname='" + brand + "' and pck=" + pck + " and color='" + color + "' and not(sph=0 and cyl=0 and axis=0)", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            array = new String[3];
            for (int i = 0; i < 3; i++)
            {
                array[i] = dv1[0][i].ToString();
            }
        }
        con.Close();
        return array;
    }
    public void Updatepriceboxpowers(String brand, Int16 pck, String color, Int16 client, Int16 doctor)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            SqlCommand sqlcom1 = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom1.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom1.CommandType = CommandType.Text;
            sqlcom.CommandText = "update box set clientprice=" + client + " where brandname='" + brand + "' and pck=" + pck + " and color='" + color + "' and not(sph=0 and cyl=0 and axis=0)";
            sqlcom1.CommandText = "update box set doctorprice=" + doctor + " where brandname='" + brand + "' and pck=" + pck + " and color='" + color + "' and not(sph=0 and cyl=0 and axis=0)";
            sqlcom.ExecuteNonQuery();
            sqlcom1.ExecuteNonQuery();
        }
        con.Close();
    }
    public String[] Showpriceboxplano(String brand, Int16 pck, String color)
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select unitprice,clientprice,doctorprice from lens where brandname='" + brand + "' and pck=" + pck + " and color='" + color + "' and sph=0 and cyl=0 and axis=0", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            array = new String[3];
            for (int i = 0; i < 3; i++)
            {
                array[i] = dv1[0][i].ToString();
            }
        }
        con.Close();
        return array;
    }
    public void Updatepriceboxplano(String brand, Int16 pck, String color, Int16 client, Int16 doctor)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            SqlCommand sqlcom1 = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom1.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom1.CommandType = CommandType.Text;
            sqlcom.CommandText = "update box set clientprice=" + client + " where brandname='" + brand + "' and pck=" + pck + " and color='" + color + "' and sph=0 and cyl=0 and axis=0";
            sqlcom1.CommandText = "update box set doctorprice=" + doctor + " where brandname='" + brand + "' and pck=" + pck + " and color='" + color + "' and sph=0 and cyl=0 and axis=0";
            sqlcom.ExecuteNonQuery();
            sqlcom1.ExecuteNonQuery();
        }
        con.Close();
    }
    public String[] Showpricesoln(String brand, Int16 bottle)
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select unitprice,clientprice,doctorprice from soln where brandname='" + brand + "' and bottlesize=" + bottle, con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            array = new String[3];
            for (int i = 0; i < 3; i++)
            {
                array[i] = dv1[0][i].ToString();
            }
        }
        con.Close();
        return array;
    }
    public void Updatepricesoln(String brand, Int16 bottle, Int16 client, Int16 doctor)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            SqlCommand sqlcom1 = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom1.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom1.CommandType = CommandType.Text;
            sqlcom.CommandText = "update soln set clientprice=" + client + " where brandname='" + brand + "' and bottlesize=" + bottle;
            sqlcom1.CommandText = "update soln set doctorprice=" + doctor + " where brandname='" + brand + "' and bottlesize=" + bottle;
            sqlcom.ExecuteNonQuery();
            sqlcom1.ExecuteNonQuery();
        }
        con.Close();
    }
    public String[] Showpriceaccs(String name)
    {
        String[] array = null;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlDataAdapter da1 = new SqlDataAdapter("select unitprice,clientprice,doctorprice from accs where name='" + name + "'", con);
            DataTable dt1 = new DataTable();
            DataView dv1;
            da1.Fill(dt1);
            dv1 = dt1.DefaultView;
            array = new String[3];
            for (int i = 0; i < 3; i++)
            {
                array[i] = dv1[0][i].ToString();
            }
        }
        con.Close();
        return array;
    }
    public void Updatepriceaccs(String name, Int16 client, Int16 doctor)
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            SqlCommand sqlcom1 = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom1.Connection = con;
            sqlcom.CommandType = CommandType.Text;
            sqlcom1.CommandType = CommandType.Text;
            sqlcom.CommandText = "update accs set clientprice=" + client + " where name='" + name + "'";
            sqlcom1.CommandText = "update accs set doctorprice=" + doctor + " where name='" + name + "'";
            sqlcom.ExecuteNonQuery();
            sqlcom1.ExecuteNonQuery();
        }
        con.Close();
    }
    public void insertbigsurgery(String name, Double age, Int16 mobile, Int16 home, Double lsph, Double lcyl, Double laxis, Double rsph, Double rcyl, Double raxis, String kind, String date, String place, Double cost, Double payed, Double expenses)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_bigsurgery";
            sqlcom.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 500));
            sqlcom.Parameters.Add(new SqlParameter("@age", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@mobile", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@home", SqlDbType.Int, 0));
            sqlcom.Parameters.Add(new SqlParameter("@lsph", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@lcyl", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@laxis", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@rsph", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@rcyl", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@raxis", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@kind", SqlDbType.NVarChar, 100));
            sqlcom.Parameters.Add(new SqlParameter("@datee", SqlDbType.Date, 0));
            sqlcom.Parameters.Add(new SqlParameter("@place", SqlDbType.NVarChar, 50));
            sqlcom.Parameters.Add(new SqlParameter("@cost", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@payed", SqlDbType.Float, 0));
            sqlcom.Parameters.Add(new SqlParameter("@expenses", SqlDbType.Float, 0));
            sqlcom.Parameters["@name"].Value = name;
            sqlcom.Parameters["@age"].Value = age;
            sqlcom.Parameters["@mobile"].Value = mobile;
            sqlcom.Parameters["@home"].Value = home;
            sqlcom.Parameters["@lsph"].Value = lsph;
            sqlcom.Parameters["@lcyl"].Value = lcyl;
            sqlcom.Parameters["@laxis"].Value = laxis;
            sqlcom.Parameters["@rsph"].Value = rsph;
            sqlcom.Parameters["@rcyl"].Value = rcyl;
            sqlcom.Parameters["@raxis"].Value = raxis;
            sqlcom.Parameters["@kind"].Value = kind;
            sqlcom.Parameters["@datee"].Value = date;
            sqlcom.Parameters["@place"].Value = place;
            sqlcom.Parameters["@cost"].Value = cost;
            sqlcom.Parameters["@payed"].Value = payed;
            sqlcom.Parameters["@expenses"].Value = expenses;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
    public void bigsurgeryrest(String name, String date, String today)
    {
        while (con.State != ConnectionState.Open)
        {
            con.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = con;
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = "sp_rest";
            sqlcom.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 500));
            sqlcom.Parameters.Add(new SqlParameter("@date", SqlDbType.Date, 0));
            sqlcom.Parameters.Add(new SqlParameter("@today", SqlDbType.Date, 0));
            sqlcom.Parameters["@name"].Value = name;
            sqlcom.Parameters["@date"].Value = date;
            sqlcom.Parameters["@today"].Value = today;
            sqlcom.ExecuteNonQuery();
        }
        con.Close();
    }
}